package org.javacore.Utils

import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.ss.usermodel.*

/**
 * Created with IntelliJ IDEA.
 * Author: Rex
 * Date: 14-5-21
 * Time: 下午2:23
 * To change this template use File | Settings | File Templates.
 */
class ExcelWithStyleUtils {

    /**
     * 生成Excel
     * @param sheetName
     * @param data
     * @param columns Map，key为属性名，value为title
     * @param workbook 如果为null，则创建新的workbook
     * @return
     */
    static Workbook generate(String sheetName, Map columns, List data) {
        Workbook workbook = new HSSFWorkbook()
        addSheet(workbook, sheetName, columns, data)
        return workbook
    }

    /**
     * 在Workbook中添加一个Sheet
     * @param workbook
     * @param sheetName
     * @param columns
     * @param data
     */
    static void addSheet(Workbook workbook, String sheetName, Map columns, List data) {
        Sheet sheet = workbook.createSheet(sheetName)

        def columnNames = columns.keySet().toList()
        def columnTitles = columns.values().toList()

        //header
        addRow(sheet, 0, columnTitles)


        Font font=workbook.createFont()
        CellStyle style=workbook.createCellStyle()
        font.setColor(IndexedColors.BLUE.getIndex())
        style.setFont(font)


        //data
        data.each { d ->
            def values  = columnNames.collect{d."$it"}

            if(d.rowStyle!=null){
                appendRowWithStyle(sheet, values,style)
            }else{
                appendRow(sheet, values)
            }
        }
    }


    static Row appendRowWithStyle(Sheet sheet, List data, CellStyle style) {
        addRowWithStyle(sheet, sheet.lastRowNum + 1, data,style)
    }

    static Row addRowWithStyle(Sheet sheet, int rowNum, List data, CellStyle style) {
        Row row = sheet.createRow(rowNum)
        data.eachWithIndex { value, idx ->
            Cell cell = row.createCell(idx)
            cell.setCellValue(value)
            cell.setCellStyle(style)
        }
        return row
    }



    static Row appendRow(Sheet sheet, List data) {
        addRow(sheet, sheet.lastRowNum + 1, data)
    }

    static Row addRow(Sheet sheet, int rowNum, List data) {
        Row row = sheet.createRow(rowNum)
        data.eachWithIndex { value, idx ->
            Cell cell = row.createCell(idx)
            cell.setCellValue(value)
        }

        return row
    }

    /***
     * 读取excel文件的数据
     * @param filePath
     * @return
     */
    static List<Map> getDateFromExcel(String filePath){
        def wrokBookReadData = []

        File file = new File(filePath)
        if(!file.exists() && !file.isFile()){
            throw new Exception("文件不存在!");
        }
        def workBook = getExcelWorkbook(new FileInputStream(file))
        getSheetListByWorkbook(workBook).each {sheet ->
            def rowList = getRowListBySheet(sheet)	//行集合
            def colCount = getFirstRowCellCount(sheet) //第一行的总列数
            def sheeetDataList = []
            if(rowList != null && !rowList.isEmpty()){
                def headTitleVal = getCellListByRow(rowList[0],colCount)
                rowList.remove(0.intValue())//去除第一行（文字标题行）
                int rowIndex = 2
                rowList.each {row->
                    int i=0;
                    def oCellVal = [:]
                    def cellList = getCellListByRow(row, colCount)
                    cellList.each {cell->
                        oCellVal."${headTitleVal[i++]}" = getValueByCell(cell)
                    }
                    oCellVal.rowIndex = rowIndex++
                    sheeetDataList << oCellVal
                }
            }
            wrokBookReadData << ['sheetName':sheet.getSheetName(),'sheetValue':sheeetDataList]
        }
        return wrokBookReadData
    }

    /***
     * 根据文件流获取Workbook 工作薄
     * @param inp
     * @return
     */
    /*static Workbook getExcelWorkbook(InputStream inp) {
        def wb = WorkbookFactory.create(inp);
        wb.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK)
        return wb
    }*/

    /**
     * 根据Workbook工作薄获取工作表
     * @param workbook
     * @return
     */
    static List<Sheet> getSheetListByWorkbook(Workbook workbook) {
        def sheetList = []
        int sheetSize = workbook.getNumberOfSheets()
        for(int i=0;i<sheetSize;i++){
            try {
                sheetList << workbook.getSheetAt(i)
            } catch (Exception e) {
                ;
            }
        }
        return sheetList
    }

    /***
     * 根据工作表sheet获取该工作表的所有行
     * @param sheet
     * @return
     */
    static List<Row> getRowListBySheet(Sheet sheet) {
        return sheet.rowIterator().collect{return it}
    }

    /***
     * 根据工作表sheet获取该工作表的第一行的列数
     * @param sheet
     * @return
     */
    /*static int getFirstRowCellCount(Sheet sheet) {
        if(sheet.getLastRowNum() == Utils.NULL_ID)
            return Utils.NULL_ID
        return sheet.getRow(0).getLastCellNum()
    }*/

    /***
     * 根据行号，列号获取该行的所有列数据
     * @param row
     * @param cellNum
     * @return
     */
    /*static List<Cell> getCellListByRow(Row row, int cellNum) {
        --cellNum
        def cellList = []
        if(cellNum >= Utils.NULL_ID){
            (0..cellNum).each {index->
                cellList << row.getCell(index)
            }
        }
        return cellList
    }*/

    /**
     * 根据该列的数据类型获取该列的值
     * @param cell
     * @return
     */
    static def getValueByCell(Cell cell) {
        if (cell.cellType == Cell.CELL_TYPE_BLANK) {
            return ""
        } else if (cell.cellType == Cell.CELL_TYPE_BOOLEAN) {
            return cell.booleanCellValue
        } else if (cell.cellType == Cell.CELL_TYPE_ERROR) {
            return cell.errorCellValue
        } else if (cell.cellType == Cell.CELL_TYPE_FORMULA) {
            return cell.cellFormula
        } else if (cell.cellType == Cell.CELL_TYPE_NUMERIC) {
            return cell.numericCellValue
        } else if (cell.cellType == Cell.CELL_TYPE_STRING) {
            return cell.stringCellValue
        } else {
            return cell.stringCellValue
        }
    }


    public static void main(String[] args){
//		println("开始读取文件....");
//		String filePath = "c:\\notes_data_model (2).xls"
//		if(filePath.trim().indexOf(".xls") == -1 && filePath.trim().indexOf(".XLS") == -1) {
//			throw new SysException("文件格式不正确!");
//		}
//
//		List<Map> list = ExcelUtils.getDateFromExcel(filePath);
//		list.each{
//			println "sheetName:${it.sheetName},sheetValue:${it.sheetValue}"
//			it.sheetValue.each{
//				println "${it}"
//				println Utils.string2Date(it.入库日期.toString(),'yyyy-MM-dd')
//			}
//		}
//		println ' 50548001 '.trim()
//		println("读取文件结束...");
    }

//	/***
//	* 该方法只适用于测试类调用。
//	* @return
//	*/
//   static List<Map> readExcel(){
//	   String filePath = "c:\\20080901.xls"
//	   if(filePath.trim().indexOf(".xls") == -1 && filePath.trim().indexOf(".XLS") == -1) {
//		   throw new SysException("文件格式不正确!");
//	   }
//	   return ExcelUtils.getDateFromExcel(filePath);
//   }
}
